using System;
using System.Collections.Generic;
using System.Text;
using SchemaExplorer;
using System.Data;
using VistaDB.Provider;
using VistaDB.DDA;

namespace Skobe.SchemaProvider
{

	public class VistaDBSchemaProvider : IDbSchemaProvider, IDisposable
	{

		IVistaDBDDA m_DDA = VistaDBEngine.Connections.OpenDDA();

		private VistaDBConnection _Connection;

		#region IDbSchemaProvider Members

		/// <summary>
		/// Gets the description for this schema provider.
		/// </summary>
		/// <value></value>
		public string Description
		{
			get { return @"Schema Provider for VistaDB 3.x"; }
		}

		/// <summary>
		/// Gets the connection for a given connection string.
		/// </summary>
		/// <param name="connectionString">The connection string.</param>
		/// <returns></returns>
		private VistaDBConnection getConnection(string connectionString)
		{
			if (_Connection == null)
			{
				_Connection = new VistaDBConnection(connectionString);
			}
			else
			{
				if (connectionString != _Connection.ConnectionString)
				{
					_Connection.Close();
					_Connection = new VistaDBConnection(connectionString);
				}
			}

			if (_Connection.State != ConnectionState.Open)
			{
				_Connection.Open();
			}

			return _Connection;
		}

		/// <summary>
		/// Gets the ce command.
		/// </summary>
		/// <param name="connectionString">The connection string.</param>
		/// <param name="commandText">The command text.</param>
		/// <returns></returns>
		private VistaDBCommand getVistaDBCommand(string connectionString, string commandText)
		{
			VistaDBConnection conn = getConnection(connectionString);
			return new VistaDBCommand(commandText, conn);
		}

		/// <summary>
		/// Gets the parameters for a given command.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="command"></param>
		/// <returns></returns>
		public ParameterSchema[] GetCommandParameters(string connectionString, CommandSchema command)
		{
			throw new Exception("The method or operation is not implemented.");
		}

		/// <summary>
		/// Gets schema information about the results of a given command.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="command"></param>
		/// <returns></returns>
		public CommandResultSchema[] GetCommandResultSchemas(string connectionString, CommandSchema command)
		{
			throw new Exception("The method or operation is not implemented.");
		}

		/// <summary>
		/// Gets the definition for a given command.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="command"></param>
		/// <returns></returns>
		public string GetCommandText(string connectionString, CommandSchema command)
		{
			throw new Exception("The method or operation is not implemented.");
		}

		/// <summary>
		/// Gets all commands for the given database.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="database"></param>
		/// <returns></returns>
		public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
		{
			throw new Exception("The method or operation is not implemented.");
		}

		/// <summary>
		/// Gets the name of the database.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <returns>The name of the database</returns>
		public string GetDatabaseName(string connectionString)
		{
			VistaDBConnection conn = getConnection(connectionString);
			return conn.Database.Trim();
		}

		/// <summary>
		/// Gets the extended properties for a given schema object.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="schemaObject"></param>
		/// <returns></returns>
		public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)
		{
			return new ExtendedProperty[0];
		}

		/// <summary>
		/// Gets all columns for a given table.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="table"></param>
		/// <returns></returns>
		/// 
		public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table)
		{
			List<ColumnSchema> columns = new List<ColumnSchema>();

			connectionString = connectionString.Remove(0, "Data Source = \"".Length - 1);
			connectionString = connectionString.Substring(0, connectionString.Length - 2);
			IVistaDBDatabase db = m_DDA.OpenDatabase(connectionString, VistaDB.VistaDBDatabaseOpenMode.NonexclusiveReadWrite, null);
			IVistaDBTableSchema tableSchema = db.TableSchema(table.Name);
			foreach (IVistaDBColumnAttributes col in tableSchema)
			{
				string nativeType = col.Type.ToString();
				DbType dataType = getDbTypeFromString(nativeType);

				int size = col.MaxLength;

				byte precision = 0;

				int scale = 0;

				bool allowNull = col.AllowNull;

				ColumnSchema s;
				if (tableSchema.Identities.Contains(col.Name))
					s = new ColumnSchema(table, col.Name, dataType, nativeType, size, precision, scale, allowNull, new ExtendedProperty[] { new ExtendedProperty("CS_IsIdentity", true, DbType.Int32)});
				else
					s = new ColumnSchema(table, col.Name, dataType, nativeType, size, precision, scale, allowNull);

				columns.Add(s);
			}
			return columns.ToArray();
		}

		/// <summary>
		/// Gets the boolean value from a  from yes/no input (ce uses them in the schema views)
		/// </summary>
		/// <param name="yesNo">The yes no.</param>
		/// <returns></returns>
		private bool getBoolFromYesNo(string yesNo)
		{
			if (yesNo == null)
			{
				return false;
			}
			return yesNo.ToUpper() == "YES";
		}

		/// <summary>
		/// Gets the db type from string.
		/// </summary>
		/// <param name="ceDataType">Type of the ce data.</param>
		/// <returns></returns>
		private System.Data.DbType getDbTypeFromString(string ceDataType)
		{
			switch (ceDataType.ToLower())
			{
				case "int":
					return System.Data.DbType.Int32;
				case "bit":
					return System.Data.DbType.Boolean;
				case "datetime":
					return System.Data.DbType.DateTime;
				case "image":
					return System.Data.DbType.Binary;
				case "money":
					return System.Data.DbType.Currency;
				case "char":
					return System.Data.DbType.AnsiStringFixedLength;
				case "nchar":
					return System.Data.DbType.StringFixedLength;
				case "float":
					return System.Data.DbType.Single;
				case "bigint":
					return System.Data.DbType.Int64;
				case "smalldatetime":
					return System.Data.DbType.DateTime;
				case "numeric":
					return System.Data.DbType.VarNumeric;
				case "real":
					return System.Data.DbType.Double;
				case "smallint":
					return System.Data.DbType.Int16;
				case "smallmoney":
					return System.Data.DbType.Currency;
				case "varchar":
					return System.Data.DbType.AnsiString;
				case "nvarchar":
					return System.Data.DbType.String;
				case "uniqueidentifier":
					return DbType.Guid;
				case "varbinary":
					return DbType.Binary;
				case "XML":
					return DbType.String;
				case "ntext":
					return DbType.String;
				default:
					throw new ArgumentOutOfRangeException("Unexpected input value: " + ceDataType);
			}
		}

		/// <summary>
		/// Gets the data from the given table.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="table"></param>
		/// <returns></returns>
		public System.Data.DataTable GetTableData(string connectionString, TableSchema table)
		{
			DataTable tbl = new DataTable(table.Name);
			string sql = "SELECT * FROM [" + table.Name + "]";
			using (VistaDBDataAdapter adapter = new VistaDBDataAdapter(getVistaDBCommand(connectionString, sql)))
			{
				adapter.Fill(tbl);
			}
			return tbl;
		}

		/// <summary>
		/// Gats all of the indexes for a given table.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="table"></param>
		/// <returns></returns>
		public IndexSchema[] GetTableIndexes(string connectionString, TableSchema table)
		{
			List<IndexSchema> indices = new List<IndexSchema>();

			connectionString = connectionString.Remove(0, "Data Source = \"".Length - 1);
			connectionString = connectionString.Substring(0, connectionString.Length - 2);
			IVistaDBDatabase db = m_DDA.OpenDatabase(connectionString, VistaDB.VistaDBDatabaseOpenMode.NonexclusiveReadWrite, null);
			IVistaDBTableSchema tableSchema = db.TableSchema(table.Name);
			foreach (IVistaDBIndexInformation idx in tableSchema.Indexes)
			{
				bool isPrimaryKey = false;
				bool isUnique = false;
				bool isClustered = false;
				List<string> memberColumns = new List<string>();

				isPrimaryKey = idx.Primary;
				isUnique = idx.Unique;

				foreach(IVistaDBKeyColumn col in idx.KeyStructure)
					memberColumns.Add(tableSchema[col.RowIndex].Name);

				indices.Add(new IndexSchema(table, idx.Name, isPrimaryKey, isUnique, isClustered, memberColumns.ToArray()));
			}
			return indices.ToArray();
		}


		/// <summary>
		/// Gets the bool from one or zero.
		/// </summary>
		/// <param name="input">The input.</param>
		/// <returns></returns>
		private bool getBoolFromOneOrZero(int input)
		{
			return !(input == 0);
		}


		/// <summary>
		/// Gets all of the table keys for a given table.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="table"></param>
		/// <returns></returns>
		public TableKeySchema[] GetTableKeys(string connectionString, TableSchema table)
		{
			return new TableKeySchema[0];
		}

		/// <summary>
		/// Gets the primary key for a given table.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="table"></param>
		/// <returns></returns>
		public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table)
		{
			connectionString = connectionString.Remove(0, "Data Source = \"".Length - 1);
			connectionString = connectionString.Substring(0, connectionString.Length - 2);
			IVistaDBDatabase db = m_DDA.OpenDatabase(connectionString, VistaDB.VistaDBDatabaseOpenMode.NonexclusiveReadWrite, null);
			IVistaDBTableSchema tableSchema = db.TableSchema(table.Name);
			foreach (IVistaDBIndexInformation idx in tableSchema.Indexes)
			{
				if (idx.Primary)
				{
					List<string> memberColumns = new List<string>();
					foreach (IVistaDBKeyColumn col in idx.KeyStructure)
						memberColumns.Add(tableSchema[col.RowIndex].Name);
					return new PrimaryKeySchema(table, idx.Name, memberColumns.ToArray());
				}
			}
			return null;
		}

		/// <summary>
		/// Gets all of the tables available in the database.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="database"></param>
		/// <returns></returns>
		public TableSchema[] GetTables(string connectionString, DatabaseSchema database)
		{
			string sql = @"SELECT name as [TableName] FROM [DATABASE SCHEMA] WHERE TypeId = 1";

			List<TableSchema> tables = new List<TableSchema>();

			using (VistaDBCommand cmd = getVistaDBCommand(connectionString, sql))
			{
				using (VistaDBDataReader results = cmd.ExecuteReader())
				{
					while (results.Read())
					{
						string tableName = ((string)results["TableName"]).Trim();
						tables.Add(new TableSchema(database, tableName, "dbo", DateTime.MinValue));
					}
				}
			}

			return tables.ToArray();
		}

		/// <summary>
		/// Gets the columns for a given view.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="view"></param>
		/// <returns></returns>
		public ViewColumnSchema[] GetViewColumns(string connectionString, ViewSchema view)
		{
			return new ViewColumnSchema[0];
		}

		/// <summary>
		/// Gets the data from a given view.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="view"></param>
		/// <returns></returns>
		public System.Data.DataTable GetViewData(string connectionString, ViewSchema view)
		{
			return null;
		}

		/// <summary>
		/// Gets the definition for a given view.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="view"></param>
		/// <returns></returns>
		public string GetViewText(string connectionString, ViewSchema view)
		{
			return "";
		}

		/// <summary>
		/// Gets all the views available for a given database.
		/// </summary>
		/// <param name="connectionString">The connection string used to connect to the target database.</param>
		/// <param name="database"></param>
		/// <returns></returns>
		public ViewSchema[] GetViews(string connectionString, DatabaseSchema database)
		{
			return new ViewSchema[0];
		}

		/// <summary>
		/// Gets the name of this schema provider.
		/// </summary>
		/// <value></value>
		public string Name
		{
			get { return "VistaDB Schema Provider"; }
		}

		/// <summary>
		/// Sets the extended properties.
		/// </summary>
		/// <param name="connectionString">The connection string.</param>
		/// <param name="schemaObject">The schema object.</param>
		public void SetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)
		{
			// not sure what this does...
		}

		#endregion

		#region IDisposable Members

		public void Dispose()
		{
			_Connection.Close();
		}

		#endregion
	}
}
